﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Linq;
using System.Data;
using DocumentFormat.OpenXml.Drawing;
using DataService.Office.ExcelDTO;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;




namespace DataService.Office.Excel
{
    public static class ExcelHepler
    {

        /// <summary>
        /// 将数据导出为Excel文件
        /// </summary>
        /// <param name="name">文件名</param>
        /// <param name="strList">数据</param>
        /// <param name="fullPath">全路径</param>
        /// <returns></returns>
        public static void ExportToExcel(DataTable dataTable, string fullPath)
        {
            int headNum = 0;//表头行数
            using (var spreadsheetDocument = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook))
            {
                var workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
                sheets.Append(sheet);


                var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                List<string> columnNameList = new List<string>();
                for (int c = 0; c < dataTable.Columns.Count; c++)
                {
                    columnNameList.Add(dataTable.Columns[c].ColumnName);
                }


                Columns sheetColumns = worksheetPart.Worksheet.Descendants<Columns>().FirstOrDefault();

                if (sheetColumns == null)
                {
                    // 如果工作表中没有 <cols> 元素，创建一个
                    sheetColumns = new Columns();
                    worksheetPart.Worksheet.Append(sheetColumns);
                }


                int picNum = 0;
                int currTableRow = headNum;
                foreach (DataRow itemDataRow in dataTable.Rows)
                {
                    currTableRow++;
                    Row row = sheetData.AppendChild(new Row());

                    SetRowHight(row, 30);

                    for (int i = 0; i < columnNameList.Count; i++)
                    {

                        object cellVal = itemDataRow[columnNameList[i]];
                        string cellVaStr = cellVal == null ? "" : cellVal.ToString();
                        if (IsImg(cellVaStr))
                        {
                            Console.WriteLine($"第{currTableRow}行，第{(i + 1)}列，{cellVaStr}");
                            picNum++;
                            Cell dataCell = row.AppendChild(new Cell());


                            int cellIndex = (i + 1);
                            dataCell.CellReference = RowColToA1(currTableRow, cellIndex);


                            SetCellWidth(sheetColumns, (uint)cellIndex, 20);

                            Console.WriteLine(cellVaStr);
                            InsertImg(worksheetPart, cellVaStr, dataCell, picNum);
                            Console.WriteLine(dataCell.CellReference);

                        }
                        else
                        {
                            Cell dataCell = new Cell
                            {
                                CellValue = new CellValue(cellVaStr),
                                DataType = new EnumValue<CellValues>(CellValues.String)
                            };

                            row.Append(dataCell);
                        }
                    }

                }
                worksheetPart.Worksheet.Save();
                workbookpart.Workbook.Save();
            }
        }


        public static void ExportToExcel(DataTable dataTable,string templateFullPath,string sheetName)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(templateFullPath, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                //查找工作薄
                Sheet sheet = SeekSheet(workbookPart, sheetName);
                //工作表
                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

                Worksheet worksheet = worksheetPart.Worksheet;
                var sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();

                int columnLength = dataTable.Columns.Count;

                Columns sheetColumns = worksheetPart.Worksheet.Descendants<Columns>().FirstOrDefault();

                if (sheetColumns == null)
                {
                    // 如果工作表中没有 <cols> 元素，创建一个
                    sheetColumns = new Columns();
                    worksheetPart.Worksheet.Append(sheetColumns);
                }

                Row lastRow = sheetData.Elements<Row>().LastOrDefault(); // 获取最后一行

                uint currLastRowIndex = (lastRow != null) ? lastRow.RowIndex  : (uint)0; // 最后一行

                // 创建新的行并追加内容
                int picNum = 0;
                int currTableRow = (int)currLastRowIndex;
                foreach (DataRow itemDataRow in dataTable.Rows)
                {
                    currTableRow++;
                    Row row = sheetData.AppendChild(new Row() { RowIndex = (uint)currTableRow });

                    SetRowHight(row, 30);

                    for (int i = 0; i < columnLength; i++)
                    {

                        object cellVal = itemDataRow[i];
                        string cellVaStr = cellVal == null ? "" : cellVal.ToString();
                        if (IsImg(cellVaStr))
                        {
                            Console.WriteLine($"第{currTableRow}行，第{(i + 1)}列，{cellVaStr}");
                            picNum++;
                            Cell dataCell = row.AppendChild(new Cell());


                            int cellIndex = (i + 1);
                            dataCell.CellReference = RowColToA1(currTableRow, cellIndex);


                            SetCellWidth(sheetColumns, (uint)cellIndex, 20);

                            Console.WriteLine(cellVaStr);
                            InsertImg(worksheetPart, cellVaStr, dataCell, picNum);
                            Console.WriteLine(dataCell.CellReference);

                        }
                        else
                        {
                            Cell dataCell = new Cell
                            {
                                CellValue = new CellValue(cellVaStr),
                                DataType = new EnumValue<CellValues>(CellValues.String)
                            };

                            row.Append(dataCell);
                        }
                    }

                }
                worksheetPart.Worksheet.Save();
                workbookPart.Workbook.Save();
            }



        }

        #region 插入图片

        private static bool IsImg(string fullPath)
        {
            string extension = System.IO.Path.GetExtension(fullPath);
            extension = extension.ToLower();
            bool isImg = extension.Equals(".jpg") || extension.Equals(".jpeg") || extension.Equals(".png") || extension.Equals(".bmp") || extension.Equals(".gif");
            bool isFile = File.Exists(fullPath);
            return isImg && isFile;
            #region 根据文件头验证图片放入业务逻辑处理
            //if (isImg && isFile)
            //{
            //    // 根据文件的头部信息，确定MIME类型
            //    const string jpeg = "FFD8FF";
            //    const string png = "89504E47";
            //    const string gif = "47494638";
            //    const string bmp = "424D";
            //    const string tiff = "49492A00"; 
            //    const string tiff2 = "4D4D002A"; 
            //    using (FileStream stream = new FileStream(fullPath, FileMode.Open, FileAccess.Read))
            //    {
            //        byte[] buffer = new byte[16];
            //        stream.Read(buffer, 0, buffer.Length);

            //        string hex = BitConverter.ToString(buffer).Replace("-", "");

            //        if (hex.StartsWith(jpeg)) return true;
            //        if (hex.StartsWith(png)) return true;
            //        if (hex.StartsWith(gif)) return true;
            //        if (hex.StartsWith(bmp)) return true;
            //        if (hex.StartsWith(tiff) || hex.StartsWith(tiff2))
            //        {
            //            // "image/tiff"
            //            return true;
            //        }
            //    }
            //}
            //return false;
            #endregion

        }
        private static void InsertImg(WorksheetPart worksheetPart, string fullImagePath, Cell cell, int picNum)
        {
            DrawingsPart drawingsPart = worksheetPart.DrawingsPart; ;
            if (drawingsPart == null)
            {
                drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
                string drawingsPartId = worksheetPart.GetIdOfPart(drawingsPart);
                worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
                { Id = drawingsPartId });
                worksheetPart.Worksheet.Save();
            }

            if (drawingsPart.WorksheetDrawing == null)
            {
                drawingsPart.WorksheetDrawing = new DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing();
            }
            ImagePart imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);

            // ImagePart imagePart = worksheetPart.AddNewPart<ImagePart>("image/jpeg");

            using (FileStream stream = new FileStream(fullImagePath, FileMode.Open))
            {
                imagePart.FeedData(stream);
            }



            string relationshipId = drawingsPart.GetIdOfPart(imagePart);
            Console.WriteLine(relationshipId);

            var ddd = new DocumentFormat.OpenXml.Drawing.BlipExtensionList(
                  new DocumentFormat.OpenXml.Drawing.BlipExtension() { Uri = "{00000000-0000-0000-0000-000000000000}" });



            CellReferenceToRowAndColumn(cell.CellReference, out int row, out int col);
            DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor());
            twoCellAnchor.EditAs = DocumentFormat.OpenXml.Drawing.Spreadsheet.EditAsValues.OneCell;

            row = row - 1;
            col = col - 1;

            // 图片的左上角和右下角的坐标
            //int x1 = Convert.ToInt32(col * 90); // 每列宽度为90EMU
            //int y1 = Convert.ToInt32(row * 240L); // 每行高度为240EMU
            //int x2 = x1 + 300; // 假设图片宽300EMU
            //int y2 = y1 + 200; // 假设图片高200EMU

            Console.WriteLine($"row：{row}，col:{col}");
            ColumnId fromColumnId = new ColumnId(col.ToString());
            RowId formRowId = new RowId(row.ToString());
            ColumnOffset fromColumnOffset = new ColumnOffset("0");  //起始 单元格 横向偏移量
            RowOffset fromRowOffset = new RowOffset("0");  //起始 单元格 纵向偏移量
            var fromMarker = new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker
            {
                ColumnId = fromColumnId,
                RowId = formRowId,
                ColumnOffset = fromColumnOffset,
                RowOffset = fromRowOffset

            };
            ColumnId toColumnId = new ColumnId((col + 1).ToString());
            RowId toRowId = new RowId((row + 1).ToString());
            ColumnOffset toColumnOffset = new ColumnOffset("0");  //结束 单元格 横向偏移量
            RowOffset toRowOffset = new RowOffset("0"); //结束 单元格 纵向偏移量
            var toMarker = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker
            {
                ColumnId = toColumnId,
                RowId = toRowId,
                ColumnOffset = toColumnOffset,
                RowOffset = toRowOffset
            };
            twoCellAnchor.FromMarker = fromMarker;
            twoCellAnchor.ToMarker = toMarker;


            var nvPicPr = new DocumentFormat.OpenXml.Drawing.NonVisualPictureProperties(
new DocumentFormat.OpenXml.Drawing.NonVisualDrawingProperties() { Id = (uint)picNum, Name = "测试图片" + picNum, Description = "描述1" });

            var cNvPicPr = new DocumentFormat.OpenXml.Drawing.NonVisualPictureDrawingProperties();
            //noChangeAspect
            cNvPicPr.Append(new PictureLocks() { NoChangeAspect = true });
            nvPicPr.Append(cNvPicPr);


            //   var offset = new DocumentFormat.OpenXml.Drawing.Offset() { X = (Int32)(x1 * 9525), Y = (Int32)(y1 * 9525) };
            // var extents = new DocumentFormat.OpenXml.Drawing.Extents() { Cx = (Int32)(x2 * 9525 - x1 * 9525), Cy = (Int32)(y2 * 9525 - y1 * 9525) };
            var offset = new DocumentFormat.OpenXml.Drawing.Offset() { X = 0, Y = 0 };
            var extents = new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0, Cy = 0 };



            var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(nvPicPr,
      new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(
          new DocumentFormat.OpenXml.Drawing.Blip(
          //ddd
          )
          { Embed = relationshipId },
          new DocumentFormat.OpenXml.Drawing.Stretch(
              new DocumentFormat.OpenXml.Drawing.FillRectangle())),
      new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(
          new DocumentFormat.OpenXml.Drawing.Transform2D(
              offset,
              extents),
          new DocumentFormat.OpenXml.Drawing.PresetGeometry(
              new DocumentFormat.OpenXml.Drawing.AdjustValueList()
              )
          { Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle }));

            twoCellAnchor.Append(picture);
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData());





            drawingsPart.WorksheetDrawing.Save();

            //// 在指定的单元格中插入图片




            ////var run = new DocumentFormat.OpenXml.Drawing.Run();
            ////run.Append(drawing);
            ////var paragraph = new Paragraph()
            ////{

            ////    //Space = new Space()
            ////    //{
            ////    //    Width = "36pt",
            ////    //    Height = "15pt"
            ////    //}
            ////};
            ////paragraph.Append(run);
            //cell.Append(drawing);

            //// 将图片插入到指定的单元格中
            //if (cell != null)
            //{
            //    cell.Append(twoCellAnchor);
            //}
        }




        #endregion

        /// <summary>
        /// 返回多Sheet数据行
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="paramDTOs">每个Sheet参数</param>
        /// <returns></returns>
        public static Dictionary<string, List<ExcelRowCell>> ReadExcel(string fileName, List<ExcelReadParamDTO> paramDTOs)
        {
            Dictionary<string, List<ExcelRowCell>> resultDatas = new Dictionary<string, List<ExcelRowCell>>();
            List<ExcelRowCell> excelRowCells = new List<ExcelRowCell>();
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                //获取spreadsheetDocument中共享的数据
                SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
                foreach (ExcelReadParamDTO item in paramDTOs)
                {
                    //查找工作薄
                    Sheet sheet = SeekSheet(workbookPart, item.SheetName);
                    //工作表
                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

                    Worksheet worksheet = worksheetPart.Worksheet;
                    var sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
                    //数据行
                    var rows = sheetData.Descendants<Row>();//获得Excel中得数据行

                    List<string> colNames = new List<string>();
                    for (int n = 1; n <= item.ColNum; n++)
                    {
                        colNames.Add(ExcelHepler.ColToA1(n));
                    }
                    int rowIndex = 0;
                    List<ExcelRowCell> resultRowDatas = new List<ExcelRowCell>();
                    foreach (Row itemRow in rows)
                    {
                        rowIndex++;
                        if (rowIndex <= item.HeadNum)
                        {
                            continue;
                        }
                        var rowCellDatas = GetRowCellValues(colNames, itemRow, workbookPart, stringTable);
                        resultRowDatas.AddRange(rowCellDatas);
                    }
                    resultDatas[item.SheetName] = resultRowDatas;
                }
            }

            return resultDatas;
        }

        /// <summary>
        /// 获取Excel数据行
        /// </summary>
        /// <param name="fileName">Excel全路径</param>
        /// <param name="headNum">几行表头</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="colNum">数据列</param>
        /// <returns></returns>
        public static List<ExcelRowCell> ReadExcel(string fileName, int headNum, int colNum, string sheetName = "")
        {
            List<ExcelRowCell> excelRowCells = new List<ExcelRowCell>();
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                //查找工作薄
                Sheet sheet = SeekSheet(workbookPart, sheetName);
                //工作表
                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

                Worksheet worksheet = worksheetPart.Worksheet;
                var sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
                //数据行
                var rows = sheetData.Descendants<Row>();//获得Excel中得数据行

                //获取spreadsheetDocument中共享的数据
                SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;

                List<string> colNames = new List<string>();
                for (int n = 1; n <= colNum; n++)
                {
                    colNames.Add(ExcelHepler.ColToA1(n));
                }
                int rowIndex = 0;
                foreach (Row itemRow in rows)
                {
                    rowIndex++;
                    if (rowIndex <= headNum)
                    {
                        continue;
                    }
                    var rowCellDatas = GetRowCellValues(colNames, itemRow, workbookPart, stringTable);
                    excelRowCells.AddRange(rowCellDatas);
                }
            }

            return excelRowCells;
        }

        public static List<ExcelRowCell> GetRowCellValues(List<string> colNames, Row sheetRow, WorkbookPart workbookPart, SharedStringTable stringTable)
        {
            List<ExcelRowCell> results = new List<ExcelRowCell>();
            uint rowIndex = sheetRow.RowIndex;

            var rowCells = sheetRow.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>();
            int colNum = 0;
            foreach (string itemColName in colNames)
            {
                colNum++;
                string tempCellReference = itemColName + rowIndex;
                ExcelRowCell excelRowCellDTO = new ExcelRowCell();

                excelRowCellDTO.CellReference = tempCellReference;
                excelRowCellDTO.RowIndex = (int)rowIndex;
                excelRowCellDTO.ColumnIndex = colNum;
                excelRowCellDTO.CellReferenceCol = itemColName;
                var tempCells = rowCells.Where(x => x.CellReference == tempCellReference);
                if (tempCells == null || tempCells.Count() == 0)
                {
                    //添加一个空值
                    excelRowCellDTO.Value = "";
                    results.Add(excelRowCellDTO);
                    continue;
                }
                Cell cell = tempCells.First();
                if (cell == null)
                {
                    //添加一个空值
                    excelRowCellDTO.Value = "";
                    results.Add(excelRowCellDTO);
                    continue;
                }
                string vale = ExcelHepler.GetCellValue(cell, workbookPart, stringTable);
                excelRowCellDTO.Value = vale;
                excelRowCellDTO.Cell = cell;
                results.Add(excelRowCellDTO);
            }
            return results;
        }

        /// <summary>
        /// 在工作薄中查找工作表
        /// </summary>
        public static Sheet SeekSheet(WorkbookPart workbookPart, string sheetName = "")
        {
            //获取所有工作薄
            IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
            Sheet sheet = null;

            if (!sheets.Any())
                throw new ArgumentException("空的Excel文档");

            if (string.IsNullOrEmpty(sheetName))
            {
                sheet = sheets.FirstOrDefault();
            }
            else
            {
                if (sheets.Count(o => o.Name == sheetName) <= 0)
                    throw new ArgumentException($"没有找到工作薄“{sheetName}”");
                sheet = sheets.First(o => o.Name == sheetName);
            }
            return sheet;
        }



        public static List<ExcelCellInfoDTO> GetExcelSheets(WorkbookPart workbookPart)
        {
            List<ExcelCellInfoDTO> sheetInfos = new List<ExcelCellInfoDTO>();
            IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
            //获取spreadsheetDocument中共享的数据
            DocumentFormat.OpenXml.Spreadsheet.SharedStringTable stringTable = workbookPart.SharedStringTablePart?.SharedStringTable;

            DocumentFormat.OpenXml.Spreadsheet.CellFormats cellFormats = ExcelHepler.GetCellFormat(workbookPart);
            DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = ExcelHepler.GetStylesheet(workbookPart);
            ThemePart themePart = workbookPart.ThemePart;
            string pattern = @"\d+";
            foreach (var itemSheet in sheets)
            {
                string name = itemSheet.Name;
                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(itemSheet.Id);
                DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;

                var sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
                //  var rows = sheetData.Elements<Row>()
                int tempRowCount = sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Row>().Count();
                //   int tempCellCount = 0;
                int defaultHeadNum = 3;

                if (tempRowCount > 0)
                {
                    Dictionary<int, ExcelEffectiveDTO> effectiveDic = GetExcelHeadInfo(workbookPart, worksheet, sheetData, cellFormats, stylesheet, themePart, stringTable, defaultHeadNum);

                    #region 获取真实列数
                    var columnArray = effectiveDic.Values.Select(x => x.ColumnIndex).ToList();
                    if (columnArray == null || columnArray.Count == 0)
                    {
                        continue;
                    }
                    int effectiveCellNum = columnArray.Max(x => x);
                    int headRowNum = effectiveDic.Count;
                    #endregion

                    #region 获取真实数据
                    int effectiveRowNum = 0;
                    Dictionary<int, List<CellInfo>> rowCellVals = new Dictionary<int, List<CellInfo>>();
                    foreach (DocumentFormat.OpenXml.Spreadsheet.Row sheetRow in sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Row>())
                    {
                        effectiveRowNum++;
                        if (effectiveRowNum > headRowNum)
                        {
                            //目前只考虑 3行表头
                            break;
                        }
                        List<CellInfo> cellInfos = new List<CellInfo>();
                        int tempColumNum = 0;
                        foreach (DocumentFormat.OpenXml.Spreadsheet.Cell sheetCell in sheetRow.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
                        {
                            tempColumNum++;
                            if (tempColumNum > effectiveCellNum)
                            {
                                break;
                            }
                            string vale = ExcelHepler.GetCellValue(sheetCell, workbookPart, stringTable);
                            if (string.IsNullOrEmpty(vale))
                            {
                                vale = "";
                            }
                            CellInfo cellInfo = new CellInfo();
                            cellInfo.CellName = vale;
                            cellInfo.CellPostion = sheetCell.CellReference;
                            cellInfos.Add(cellInfo);
                        }
                        rowCellVals[effectiveRowNum] = cellInfos;
                    }

                    #endregion


                    #region 表头

                    int headNum = rowCellVals.Keys.Count;
                    //1.多表头合并
                    //1.1先判断是多表头还是单表头
                    List<CellInfo> firstRow = new List<CellInfo>();
                    if (rowCellVals.ContainsKey(1))
                    {
                        firstRow = rowCellVals[1] ?? new List<CellInfo>();
                    }
                    List<CellInfo> secondRow = new List<CellInfo>();
                    if (rowCellVals.ContainsKey(2))
                    {
                        secondRow = rowCellVals[2] ?? new List<CellInfo>();
                    }
                    List<CellInfo> thirdRow = new List<CellInfo>();
                    if (rowCellVals.ContainsKey(3))
                    {
                        thirdRow = rowCellVals[3] ?? new List<CellInfo>();
                    }
                    List<CellInfo> fourthRow = new List<CellInfo>();
                    if (rowCellVals.ContainsKey(4))
                    {
                        fourthRow = rowCellVals[4] ?? new List<CellInfo>();
                    }
                    // bool isOne = firstRow[0].CellName == "序号" && secondRow[0].CellName == "1";
                    // bool isTwo = firstRow[0].CellName == "序号" && string.IsNullOrEmpty(secondRow[0].CellName) && (thirdRow[0].CellName == "1" || string.IsNullOrEmpty(thirdRow[0].CellName));
                    // bool ist = secondRow[0].CellName == "序号" && string.IsNullOrEmpty(firstRow[0].CellName) && (thirdRow[0].CellName == "1" || string.IsNullOrEmpty(thirdRow[0].CellName));
                    // bool isThree = secondRow[0].CellName == "序号" && string.IsNullOrEmpty(thirdRow[0].CellName) && (fourthRow[0].CellName == "1" || string.IsNullOrEmpty(fourthRow[0].CellName));
                    // bool isth = thirdRow[0].CellName == "序号" && string.IsNullOrEmpty(secondRow[0].CellName) && (fourthRow[0].CellName == "1" || string.IsNullOrEmpty(fourthRow[0].CellName));
                    if (headNum == 1)
                    {
                        //单表头 
                        ExcelCellInfoDTO excelCellInfoDTO = new ExcelCellInfoDTO();
                        excelCellInfoDTO.SheetName = itemSheet.Name;
                        excelCellInfoDTO.CellInfos = firstRow;
                        excelCellInfoDTO.HeadRowNum = headNum;
                        excelCellInfoDTO.EffectiveCellNum = effectiveCellNum;
                        sheetInfos.Add(excelCellInfoDTO);
                    }
                    else if (headNum == 2)
                    {
                        //两行表头
                        ExcelCellInfoDTO excelCellInfoDTO = new ExcelCellInfoDTO();
                        excelCellInfoDTO.SheetName = itemSheet.Name;
                        excelCellInfoDTO.HeadRowNum = headNum;
                        //两行表头
                        List<CellInfo> cellInfos = GetHeaderCellInfo(firstRow, secondRow);
                        excelCellInfoDTO.CellInfos = cellInfos;
                        excelCellInfoDTO.EffectiveCellNum = effectiveCellNum;
                        sheetInfos.Add(excelCellInfoDTO);
                    }
                    else if (headNum == 3)
                    {
                        //三行表头
                        ExcelCellInfoDTO excelCellInfoDTO = new ExcelCellInfoDTO();
                        excelCellInfoDTO.SheetName = itemSheet.Name;
                        excelCellInfoDTO.HeadRowNum = headNum;

                        List<CellInfo> cellInfos = GetHeaderCellInfo(secondRow, thirdRow);
                        excelCellInfoDTO.CellInfos = cellInfos;
                        excelCellInfoDTO.EffectiveCellNum = effectiveCellNum;
                        sheetInfos.Add(excelCellInfoDTO);


                    }
                    #endregion

                }
            }
            return sheetInfos;
        }


        public static Dictionary<int, ExcelEffectiveDTO> GetExcelHeadInfo(WorkbookPart workbookPart, Worksheet worksheet, DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData, CellFormats cellFormats, Stylesheet stylesheet, ThemePart themePart, SharedStringTable stringTable, int defaultHeadNum)
        {
            string pattern = @"\d+";
            int rowIndex = 0;
            int firstEffectiveNum = 0;
            string firstLastCellReference = string.Empty;
            Dictionary<int, ExcelEffectiveDTO> effectiveDic = new Dictionary<int, ExcelEffectiveDTO>();
            // 遍历Excel工作表中的单元格并将它们添加到Word表格中
            foreach (DocumentFormat.OpenXml.Spreadsheet.Row sheetRow in sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Row>())
            {
                rowIndex++;
                if (rowIndex > defaultHeadNum)
                {
                    //目前只考虑 3行表头
                    break;
                }
                //   tempCellCount = 0;


                List<CellInfo> cellInfos = new List<CellInfo>();
                bool isHaveBold = false;
                int allEffectiveNum = 0;
                string allCellReference = string.Empty;
                foreach (DocumentFormat.OpenXml.Spreadsheet.Cell sheetCell in sheetRow.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
                {

                    //  tempCellCount++;
                    var isBold = GetIsBold(cellFormats, stylesheet, sheetCell);
                    string vale = ExcelHepler.GetCellValue(sheetCell, workbookPart, stringTable);
                    if (string.IsNullOrEmpty(vale))
                    {
                        vale = "";
                    }
                    if (isBold)
                    {
                        if (!string.IsNullOrEmpty(vale))
                        {
                            isHaveBold = true;
                        }
                    }
                    if (!string.IsNullOrWhiteSpace(vale) && !string.IsNullOrEmpty(vale))
                    {
                        allEffectiveNum++;
                        allCellReference = sheetCell.CellReference;
                    }
                    if (rowIndex == 1 && !string.IsNullOrWhiteSpace(vale) && !string.IsNullOrEmpty(vale))
                    {
                        firstEffectiveNum++;
                        firstLastCellReference = sheetCell.CellReference;
                    }
                }
                if (isHaveBold)
                {
                    ExcelEffectiveDTO excelEffectiveDTO = new ExcelEffectiveDTO();
                    excelEffectiveDTO.EffectiveNum = allEffectiveNum;
                    excelEffectiveDTO.ColumnCellReference = allCellReference;
                    string newCellPostion = Regex.Replace(allCellReference, pattern, "");
                    excelEffectiveDTO.ColumnCellIndexFormate = newCellPostion;

                    CellReferenceToRowAndColumn(allCellReference, out int row, out int col);
                    excelEffectiveDTO.RowIndex = row;
                    excelEffectiveDTO.ColumnIndex = col;
                    effectiveDic[rowIndex] = excelEffectiveDTO;
                }

            }
            if (effectiveDic.Count == 0)
            {
                //没有找到加粗的表头，默认取第一行 作为表头
                if (!string.IsNullOrEmpty(firstLastCellReference))
                {
                    ExcelEffectiveDTO excelEffectiveDTO = new ExcelEffectiveDTO();
                    excelEffectiveDTO.EffectiveNum = firstEffectiveNum;
                    excelEffectiveDTO.ColumnCellReference = firstLastCellReference;
                    string newCellPostion = Regex.Replace(firstLastCellReference, pattern, "");
                    excelEffectiveDTO.ColumnCellIndexFormate = newCellPostion;

                    CellReferenceToRowAndColumn(firstLastCellReference, out int row, out int col);
                    excelEffectiveDTO.RowIndex = row;
                    excelEffectiveDTO.ColumnIndex = col;
                    effectiveDic[1] = excelEffectiveDTO;
                }
            }
            return effectiveDic;
        }

        public static List<ExcelRowParamDTO> GetMergeCells(Worksheet worksheetPart)
        {
            string pattern = @"\d+";
            MergeCells mergeCells = worksheetPart.Elements<MergeCells>().FirstOrDefault();
            List<ExcelRowParamDTO> mergeCellResults = new List<ExcelRowParamDTO>();
            if (mergeCells != null)
            {
                foreach (MergeCell mergeCell in mergeCells.Elements<MergeCell>())
                {
                    ExcelRowParamDTO excelRowParamDTO = new ExcelRowParamDTO();
                    excelRowParamDTO.CellPostion = mergeCell.Reference;
                    var tempArray = excelRowParamDTO.CellPostion.Split(':');

                    string start = Regex.Replace(tempArray[0], pattern, "");
                    string end = Regex.Replace(tempArray[1], pattern, "");
                    if (start == end)
                    {
                        excelRowParamDTO.Direction = MegerDirectionEnum.纵向合并;
                    }
                    else
                    {
                        excelRowParamDTO.Direction = MegerDirectionEnum.横向合并;
                    }

                    excelRowParamDTO.StartPotionCol = start;
                    excelRowParamDTO.EndPotionCol = end;
                    CellReferenceToRowAndColumn(tempArray[0], out int startRow, out int startCol);
                    CellReferenceToRowAndColumn(tempArray[1], out int endRow, out int endCol);
                    excelRowParamDTO.StartPotion = tempArray[0];
                    excelRowParamDTO.EndPotion = tempArray[1];

                    excelRowParamDTO.StartPotion_RowIndex = startRow;
                    excelRowParamDTO.StartPotion_ColIndex = startCol;

                    excelRowParamDTO.EndPotion_RowIndex = endRow;
                    excelRowParamDTO.EndPotion_ColIndex = endCol;
                    mergeCellResults.Add(excelRowParamDTO);
                }
            }

            return mergeCellResults;

        }

        private static List<CellInfo> GetHeaderCellInfo(List<CellInfo> currentCellInfos, List<CellInfo> nextCellInfos)
        {


            List<CellInfo> resultCellInfos = new List<CellInfo>();
            for (int i = 0; i < currentCellInfos.Count; i++)
            {
                CellInfo itemFirtRow = currentCellInfos[i];
                string firstRowVal = itemFirtRow.CellName;
                var secondCellInfo = nextCellInfos[i] ?? new CellInfo();
                //纵向合并（值 可能在第一行，也可能在第二行，也有可能两行都有）；横向合并
                if (!string.IsNullOrEmpty(firstRowVal) && string.IsNullOrEmpty(secondCellInfo.CellName))
                {
                    CellInfo cellInfo = new CellInfo();
                    cellInfo.CellName = firstRowVal;
                    cellInfo.CellPostion = itemFirtRow.CellPostion;
                    resultCellInfos.Add(cellInfo);

                }
                if (string.IsNullOrEmpty(firstRowVal) && !string.IsNullOrEmpty(secondCellInfo.CellName))
                {
                    CellInfo cellInfo = new CellInfo();
                    cellInfo.CellName = secondCellInfo.CellName;
                    cellInfo.CellPostion = secondCellInfo.CellPostion;
                    resultCellInfos.Add(cellInfo);
                }
                if (!string.IsNullOrEmpty(firstRowVal) && !string.IsNullOrEmpty(secondCellInfo.CellName))
                {
                    CellInfo cellInfo = new CellInfo();
                    cellInfo.CellName = firstRowVal + "_" + secondCellInfo.CellName;
                    cellInfo.CellPostion = secondCellInfo.CellPostion;
                    resultCellInfos.Add(cellInfo);

                }



            }
            return resultCellInfos;
        }


        #region 单元格属性
        /// <summary>
        /// 修改 行高
        /// </summary>
        /// <param name="row"></param>
        /// <param name="height"></param>
        public static void SetRowHight(Row row, int height)
        {
            row.Height = height;
            row.CustomHeight = true;//指示受影响列 () 的列宽不同于默认值或已手动设置的标志
        }
        /// <summary>
        /// 修改单元格宽度
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="columnIndex"></param>
        /// <param name="columnWidth"></param>
        public static void SetCellWidth(Columns columns, uint columnIndex, int columnWidth)
        {

            // 检查并获取列元素，如果没有则创建
            Column column = columns.Descendants<Column>().FirstOrDefault(c => c.Min == columnIndex && c.Max == columnIndex);

            if (column == null)
            {
                // 创建新的列元素
                column = new Column()
                {
                    Min = columnIndex,
                    Max = columnIndex,
                    Width = columnWidth, // 设置列宽度
                    CustomWidth = true
                };
                columns.Append(column);
            }
            else
            {
                // 如果列已经存在，修改其宽度
                column.Width = columnWidth;
                column.CustomWidth = true;
            }

        }
        #endregion
        /// <summary>
        /// 获取单位格的值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="workbookPart"></param>
        /// <param name="type">1 不去空格 2 前后空格 3 所有空格  </param>
        /// <returns></returns>
        public static string GetCellValue(Cell cell, WorkbookPart workbookPart, SharedStringTable stringTable, int type = 2)
        {
            //合并单元格不做处理
            if (cell.CellValue == null)
                return string.Empty;

            string cellInnerText = cell.CellValue.InnerText;
            //纯字符串
            if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.Number))
            {
                if (cell.DataType.Value == CellValues.SharedString && !stringTable.Any())
                {
                    throw new ArgumentException("获取模板数据失败");
                }


                bool isParse = int.TryParse(cellInnerText, out int tempIndex);
                if (!isParse)
                {
                    // Console.WriteLine($"{cell.CellValue?.InnerText}哈哈哈哈");
                    return cellInnerText;
                }
                string text = stringTable.ElementAt(tempIndex).InnerText;
                if (type == 2)
                    return text.Trim();
                else if (type == 3)
                    return text.Replace(" ", "");
                else
                    return text;
            }
            if (cell.DataType != null && cell.DataType.Value == CellValues.String)
            {
                return cellInnerText;
            }
            //bool类型
            else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
            {
                return (cellInnerText != "0").ToString().ToUpper();
            }
            else if (cell.DataType == null)
            {
                return cellInnerText;
            }
            //数字格式代码（numFmtId）小于164是内置的：https://www.it1352.com/736329.html
            else
            {
                //为空为数值
                if (cell.StyleIndex == null)
                    return cellInnerText;

                Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
                CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];

                uint formatId = cellFormat.NumberFormatId.Value;
                double doubleTime;//OLE 自动化日期值
                DateTime dateTime;//yyyy/MM/dd HH:mm:ss
                switch (formatId)
                {
                    case 0://常规
                        return cellInnerText;
                    case 9://百分比【0%】
                    case 10://百分比【0.00%】
                    case 11://科学计数【1.00E+02】
                    case 12://分数【1/2】
                        return cellInnerText;
                    case 14:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    //case 15:
                    //case 16:
                    case 17:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM");
                    //case 18:
                    //case 19:
                    case 20:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("H:mm");
                    case 21:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("HH:mm:ss");
                    case 22:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd HH:mm");
                    //case 45:
                    //case 46:
                    case 47:
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    case 58://【中国】11月11日
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("MM/dd");
                    case 176://【中国】2020年11月11日
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("yyyy/MM/dd");
                    case 177://【中国】11:22:00
                        doubleTime = double.Parse(cellInnerText);
                        dateTime = DateTime.FromOADate(doubleTime);
                        return dateTime.ToString("HH:mm:ss");
                    default:
                        return cellInnerText;
                }
            }

        }

        /// <summary>
        /// 单元格字符 转换为 行和 如 A2=第2行，第A列
        /// </summary>
        /// <param name="cellReference"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        public static void CellReferenceToRowAndColumn(string cellReference, out int row, out int col)
        {
            col = 0;
            row = 0;
            for (int i = 0; i < cellReference.Length; i++)
            {
                if (Char.IsLetter(cellReference[i]))
                {
                    col = col * 26 + (cellReference[i] - 'A' + 1);
                }
                else if (Char.IsDigit(cellReference[i]))
                {
                    row = int.Parse(cellReference.Substring(i));
                    break;
                }
            }
        }
        public static string RowColToA1(int row, int col)
        {
            if (row < 1 || col < 1)
                throw new ArgumentException("Row and column must be greater than zero.");

            string columnName = "";
            int colRemain;

            // 将列数转换为列的字母表示
            do
            {
                colRemain = col - 1;
                col = colRemain / 26;
                char letter = (char)('A' + (colRemain % 26));
                columnName = letter + columnName;
            }
            while (col > 0);

            return columnName + row;
        }

        public static string ColToA1(int col)
        {
            if (col < 1)
                throw new ArgumentException("Row and column must be greater than zero.");

            string columnName = "";
            int colRemain;

            // 将列数转换为列的字母表示
            do
            {
                colRemain = col - 1;
                col = colRemain / 26;
                char letter = (char)('A' + (colRemain % 26));
                columnName = letter + columnName;
            }
            while (col > 0);

            return columnName;
        }



        #region  获取字体样式
        public static ExcelRowCellStyleDTO GetCellFont(CellFormats cellFormats, Stylesheet styleSheet, ThemePart themePart, Cell cell)
        {

            ExcelRowCellStyleDTO excelRowCellStyleDTO = new ExcelRowCellStyleDTO();

            string backColor = GetCellFontBackGround(cellFormats, styleSheet, themePart, cell);
            excelRowCellStyleDTO.BackColorName = backColor;

            string color = GetCellFontColor(cellFormats, styleSheet, cell);
            excelRowCellStyleDTO.ColorName = color;

            bool isBold = GetIsBold(cellFormats, styleSheet, cell);
            excelRowCellStyleDTO.FontBold = isBold;
            return excelRowCellStyleDTO;


        }
        /// <summary>
        /// 颜色是使用了theme（主题色）和tint（插值）来表示。
        /// 得出Excel单元格背景色的两种表现方式：rgb和theme + tint（官方没有解释，只能自己查看生成规则，搞了很久。。。）
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetCellFontBackGround(CellFormats cellFormats, Stylesheet styleSheet, ThemePart themePart, Cell cell)
        {
            string resultBackGround = string.Empty;
            if (cell == null || cell.StyleIndex == null) return resultBackGround;
            if (cellFormats == null)
            {
                return resultBackGround;
            }
            if (styleSheet == null)
            {
                return resultBackGround;
            }
            if (themePart == null)
            {
                return resultBackGround;
            }
            CellFormat cellFormat = (CellFormat)cellFormats.ElementAt((int)cell.StyleIndex.Value);
            if (cellFormat == null || cellFormat.FillId == null)
            {
                return resultBackGround;
            }
            if (styleSheet.Fills == null)
            {
                return resultBackGround;
            }
            var fill = (DocumentFormat.OpenXml.Spreadsheet.Fill)styleSheet.Fills.ElementAt((int)cellFormat.FillId.Value);
            if (fill == null || fill.PatternFill == null)
            {
                return resultBackGround;
            }
            DocumentFormat.OpenXml.Spreadsheet.PatternFill patternFill = (DocumentFormat.OpenXml.Spreadsheet.PatternFill)fill.PatternFill;
            if (themePart == null)
            {
                return resultBackGround;
            }
            Theme theme = themePart.Theme;
            if (theme == null)
            {
                return resultBackGround;
            }
            if (patternFill != null && patternFill.PatternType != null && patternFill.PatternType.Value == PatternValues.Solid)
            {
                if (patternFill.ForegroundColor != null)
                {
                    if (patternFill.ForegroundColor.Rgb != null)
                    {
                        resultBackGround = "#" + patternFill.ForegroundColor.Rgb.Value;
                    }
                    else if (patternFill.ForegroundColor.Theme != null)
                    {
                        if (theme.ThemeElements == null)
                        {
                            return resultBackGround;
                        }
                        var colorScheme = theme.ThemeElements.ColorScheme;
                        if (colorScheme == null)
                        {
                            return resultBackGround;
                        }
                        // 主题色获取

                        var color2Type = (Color2Type)colorScheme.ElementAt((int)patternFill.ForegroundColor.Theme.Value);
                        if (color2Type == null)
                        {
                            return resultBackGround;
                        }
                        if (color2Type.RgbColorModelHex == null)
                        {
                            return resultBackGround;
                        }
                        string originalColor = color2Type.RgbColorModelHex.Val;
                        if (string.IsNullOrEmpty(originalColor))
                        {
                            return resultBackGround;
                        }
                        if (patternFill.ForegroundColor.Tint != null)
                        {
                            // 颜色计算
                            resultBackGround = CalculateTintedColor(originalColor, patternFill.ForegroundColor.Tint);
                        }
                        else
                        {
                            resultBackGround = "#" + originalColor;
                        }
                    }
                }
            }
            return resultBackGround;
        }

        public static string CalculateTintedColor(string originalColor, double tint)
        {
            // RGB转换
            int red = Convert.ToInt32(originalColor.Substring(0, 2), 16);
            int green = Convert.ToInt32(originalColor.Substring(2, 2), 16);
            int blue = Convert.ToInt32(originalColor.Substring(4, 2), 16);
            int interpolatedRed = 0;
            int interpolatedGreen = 0;
            int interpolatedBlue = 0;
            // 基于tint正负值的颜色计算
            if (tint > 0)
            {
                // 白色
                int white = 255;
                // 插值计算
                interpolatedRed = (int)Math.Round(red * (1 - tint) + white * tint);
                interpolatedGreen = (int)Math.Round(green * (1 - tint) + white * tint);
                interpolatedBlue = (int)Math.Round(blue * (1 - tint) + white * tint);
            }
            else
            {
                // 黑色
                int black = 0;
                // 插值计算
                interpolatedRed = (int)Math.Round(red * (1 + tint));
                interpolatedGreen = (int)Math.Round(green * (1 + tint));
                interpolatedBlue = (int)Math.Round(blue * (1 + tint));
                // 防止出现计算结果小于0的情况
                interpolatedRed = Math.Max(interpolatedRed, black);
                interpolatedGreen = Math.Max(interpolatedGreen, black);
                interpolatedBlue = Math.Max(interpolatedBlue, black);
            }
            // 计算结束后转化为16进制颜色
            string interpolatedColor = $"#{interpolatedRed:X2}{interpolatedGreen:X2}{interpolatedBlue:X2}";
            return interpolatedColor;
        }

        public static bool GetIsBold(CellFormats cellFormats, Stylesheet styleSheet, Cell cell)
        {
            var cellFont = GetFontCell(cellFormats, styleSheet, cell);
            if (cellFont == null)
            {
                return false;
            }
            if (cellFont.Bold == null)
            {
                //没有加粗
                return false;
            }
            if (cellFont.Bold != null || cellFont.Bold.Val == true)
            {
                return true;
            }
            return false;
        }
        public static DocumentFormat.OpenXml.Spreadsheet.Font GetFontCell(CellFormats cellFormats, Stylesheet styleSheet, Cell cell)
        {
            if (cellFormats == null)
            {
                return null;
            }
            if (styleSheet == null)
            {
                return null;
            }
            if (styleSheet.Fonts == null)
            {
                return null;
            }
            uint styleIndex = cell.StyleIndex?.Value ?? 0;
            // 根据索引获取对应的CellFormat
            CellFormat cellFormat = cellFormats.Elements<CellFormat>().ElementAt((int)styleIndex);
            // 获取字体索引
            uint fontIndex = cellFormat.FontId?.Value ?? 0;
            // 获取对应的Font和Fill信息（如果存在）
            DocumentFormat.OpenXml.Spreadsheet.Font font = styleSheet.Fonts.Elements<DocumentFormat.OpenXml.Spreadsheet.Font>().ElementAt((int)fontIndex);
            return font;
        }
        public static string GetCellFontColor(CellFormats cellFormats, Stylesheet styleSheet, Cell cell)
        {
            string resultColorVal = string.Empty;
            if (cellFormats == null)
            {
                return resultColorVal;
            }
            if (styleSheet == null)
            {
                return resultColorVal;
            }
            if (styleSheet.Fonts == null)
            {
                return resultColorVal;
            }
            uint styleIndex = cell.StyleIndex?.Value ?? 0;
            // 根据索引获取对应的CellFormat
            CellFormat cellFormat = cellFormats.Elements<CellFormat>().ElementAt((int)styleIndex);
            // 获取字体索引
            uint fontIndex = cellFormat.FontId?.Value ?? 0;
            // 获取对应的Font和Fill信息（如果存在）
            DocumentFormat.OpenXml.Spreadsheet.Font font = styleSheet.Fonts.Elements<DocumentFormat.OpenXml.Spreadsheet.Font>().ElementAt((int)fontIndex);

            // 获取字体颜色
            var fontColor = font.Elements<DocumentFormat.OpenXml.Spreadsheet.ForegroundColor>().FirstOrDefault();
            if (fontColor != null && fontColor.Rgb != null)
            {
                resultColorVal = fontColor.Rgb.InnerText;
            }
            if (font.Color != null && font.Color.Rgb != null)
            {
                resultColorVal = font.Color.Rgb.Value;
            }
            return resultColorVal;
        }
        #endregion

        public static CellFormats GetCellFormat(WorkbookPart workbookPart)
        {
            var workbookStylesPart = workbookPart.WorkbookStylesPart;
            if (workbookStylesPart == null)
            {
                return null;
            }
            Stylesheet styleSheet = workbookStylesPart.Stylesheet;
            if (styleSheet == null)
            {
                return null;
            }
            // 获取CellFormats
            CellFormats cellFormats = styleSheet.CellFormats;
            return cellFormats;
        }

        public static Stylesheet GetStylesheet(WorkbookPart workbookPart)
        {
            var workbookStylesPart = workbookPart.WorkbookStylesPart;
            if (workbookStylesPart == null)
            {
                return null;
            }
            Stylesheet styleSheet = workbookStylesPart.Stylesheet;
            return styleSheet;
        }


    }
}
